﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class CompanyRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<CompanyModel> GetAllCompanies()
        {
            List<CompanyModel> allCompanies = new List<CompanyModel>();
            String query = "SELECT * FROM " + Configuration.DatabaseName + ".company";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    CompanyModel company = new CompanyModel();

                    company.CompanyID = int.Parse(dataReader["in_fldCompanyID"] + "");
                    company.CompanyName = dataReader["vc_fldCompanyName"] + "";
                    company.Address1 = dataReader["vc_fldAddress1"] + "";
                    company.Address2 = dataReader["vc_fldAddress2"] + "";
                    company.Address3 = dataReader["vc_fldAddress3"] + "";
                    company.Address4 = dataReader["vc_fldAddress4"] + "";
                    company.Address5 = dataReader["vc_fldAddress5"] + "";
                    company.TelephoneNo = dataReader["vc_fldTelephoneNo"] + "";
                    company.FaxNo = dataReader["vc_fldFaxNo"] + "";

                    allCompanies.Add(company);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allCompanies;
        }

        public CompanyModel GetCompany(String CompanyName)
        {
            CompanyModel com = new CompanyModel();

            List<CompanyModel> companiesList = GetAllCompanies();

            foreach (CompanyModel item in companiesList)
            {
                if (item.CompanyName == CompanyName)
                {
                    com = item;
                    break;
                }
            }

            return com;
        }

        public CompanyModel GetCompany(int CompanyID)
        {
            CompanyModel com = new CompanyModel();

            List<CompanyModel> companiesList = GetAllCompanies();

            foreach (CompanyModel item in companiesList)
            {
                if (item.CompanyID == CompanyID)
                {
                    com = item;
                    break;
                }
            }

            return com;
        }

        public bool Insert(CompanyModel comModel)
        {
            bool isInserted = false;

            try
            {
                String query = "INSERT INTO " + Configuration.DatabaseName + ".company (vc_fldCompanyName, vc_fldAddress1, vc_fldAddress2,"
                    + "vc_fldAddress3, vc_fldAddress4, vc_fldAddress5, vc_fldTelephoneNo, vc_fldFaxNo) VALUES('" + comModel.CompanyName + "', '" + comModel.Address1 + "', '"
                    + comModel.Address2 + "', '" + comModel.Address3 + "', '" + comModel.Address4 + "', '" + comModel.Address5 + "', '"
                    + comModel.TelephoneNo + "', '" + comModel.FaxNo + "')";
                db.DBConnectionOn();
                //create command and assign the query and connection from the constructor
                MySqlCommand cmd = new MySqlCommand(query, db.connection);

                //Execute command
                cmd.ExecuteNonQuery();

                isInserted = true;
            }
            catch(Exception e)
            {
                isInserted = false;
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }

            return isInserted;
        }

        public IEnumerable<SelectListItem> GetCompaniesForDropDownList(List<CompanyModel> modelList)
        {
            List<SelectListItem> CommanyList = new List<SelectListItem>();

            foreach (CompanyModel company in modelList)
            {
                SelectListItem sListItem = new SelectListItem();
                sListItem.Text = company.CompanyName;
                sListItem.Value = company.CompanyName;

                CommanyList.Add(sListItem);
            }

            return CommanyList;
        }
    }
}